import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

warehouse_inv_router = APIRouter(prefix="/material/warehouseInv", tags=["储物柜管理"],
                                 dependencies=[Depends(oauth2_scheme)])


class LockerInventorySearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fbatch: Optional[str] = Field("", description="批次号")
    fboxbarcode: Optional[str] = Field("", description="箱子条码")
    fcabbarcode: Optional[str] = Field("", description="柜子条码")
    fgoodno: Optional[str] = Field("", description="物料编号")
    frfidbarcode: Optional[str] = Field("", description="RFID条码")
    ftype: Optional[str] = Field("1", description="类型，默认1")


@warehouse_inv_router.get("/list", summary="储物柜库存查询接口")
def list_warehouse_inv(search: LockerInventorySearch = Query(), db: Session = Depends(get_db)):
    sql = "select count(1) from proc_cab_dogetgoodinv( :ftype , :fcabbarcode , :fboxbarcode , :frfidbarcode , :fbatch , :fgoodno ,'', :site_no ) a,lims.i_ts_cabinet b"
    sql = sql + " where 1=1"
    sql = sql + " and a.fcabbarcode=b.fequipmentbarcode"
    count = db.execute(text(sql), {
        "ftype": search.ftype,
        "fcabbarcode": search.fcabbarcode,
        "fboxbarcode": search.fboxbarcode,
        "frfidbarcode": search.frfidbarcode,
        "fbatch": search.fbatch,
        "fgoodno": search.fgoodno,
        "site_no": search.fsiteNo,
    }).scalar()

    sql = "select fcabbarcode,fcabname,fboxbarcode,a.fequipmenttag,fbatch,frfidbarcode"
    sql = sql + ",fgoodno,fgoodname,a.fstate,a.foperator,a.fupddate,fprocdate,f1,f2,f3,f4,f5,f6"
    sql = sql + ",b.fsite_no,b.fpoint_no"
    sql = sql + " from proc_cab_dogetgoodinv( :ftype , :fcabbarcode , :fboxbarcode , :frfidbarcode , :fbatch , :fgoodno ,'', :site_no ) a,lims.i_ts_cabinet b"
    sql = sql + " where 1=1"
    sql = sql + " and a.fcabbarcode=b.fequipmentbarcode"
    sql = sql + " order by  a.fcabbarcode,a.fboxbarcode,fbatch "
    sql = sql + " limit :limit offset :offset "
    data = db.execute(text(sql), {
        "ftype": search.ftype,
        "fcabbarcode": search.fcabbarcode,
        "fboxbarcode": search.fboxbarcode,
        "frfidbarcode": search.frfidbarcode,
        "fbatch": search.fbatch,
        "fgoodno": search.fgoodno,
        "site_no": search.fsiteNo,
        "limit": search.limit,
        "offset": search.offset,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fcabbarcode'] = data[key][0]
        d1['fcabname'] = data[key][1]
        d1['fboxbarcode'] = data[key][2]
        d1['fequipmenttag'] = data[key][3]
        d1['fbatch'] = data[key][4]
        d1['frfidbarcode'] = data[key][5]
        d1['fgoodno'] = data[key][6]
        d1['fgoodname'] = data[key][7]
        d1['fstate'] = data[key][8]
        d1['foperator'] = data[key][9]
        d1['fupddate'] = data[key][10]
        d1['fprocdate'] = data[key][11]
        d1['f1'] = float(data[key][12])
        d1['f2'] = float(data[key][13])
        d1['f3'] = float(data[key][14])
        d1['f4'] = float(data[key][15])
        d1['f5'] = float(data[key][16])
        d1['f6'] = float(data[key][17])
        d1['fsiteNo'] = data[key][18]
        d1['fpointNo'] = data[key][19]
        d1['fbuyprice'] = None
        d1['fdictmemo'] = None
        d1['fdictno'] = None
        d1['fpointName'] = None
        d1['fqty'] = None
        d1['rukudate'] = None
        d1['rukudateStr'] = None
        d1['towername'] = None
        d1['validday'] = None
        data_list.append(d1)
    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class HouseSearch(BaseModel):
    fsiteNo: str = Field("", description="站点编号")
    fbatch: str = Field("", description="批次号")


@warehouse_inv_router.get("/docalwarehouseinvdtl", summary="储物柜库存明细查询接口")
def list_docalwarehouseinvd(search: HouseSearch = Query(), db: Session = Depends(get_db)):
    sql = "select to_char(a.fupddate,'yyyy-mm-dd hh24:mi:ss') fupddate,c.username,b.fdictname,a.fqty"
    sql = sql + " from i_warehouse_dtl a"
    sql = sql + " left join s_ts_dict b on b.fdicttype=3004 and b.fdictno=cast(fbiztype as varchar)"
    sql = sql + " left join am_sys_user c on cast(c.id as varchar)=a.foperator"
    sql = sql + " where 1=1"
    sql = sql + " and a.fsite_no= :site_no "
    sql = sql + " and a.fbatch= :fbatch "
    sql = sql + " order by a.fupddate"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "fbatch": search.fbatch,
    }).fetchall()
    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fupddate'] = data[key][0]
        d1['username'] = data[key][1]
        d1['fdictname'] = data[key][2]
        d1['fqty'] = data[key][3]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)
